package at.ac.tuwien.ifs.calendit.basis.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.MappingSqlQuery;
import org.springframework.jdbc.object.SqlUpdate;
import org.springframework.transaction.PlatformTransactionManager;

import at.ac.tuwien.ifs.calendit.dao.IAbteilung;
import at.ac.tuwien.ifs.calendit.domain.Abteilung;
import at.ac.tuwien.ifs.calendit.domain.Mitarbeiter;

public class JDBCAbteilungDAO implements IAbteilung{

	
	/**
	* Retrieves the logger for this class.
	*/
	private static Log log = LogFactory.getLog(JDBCAbteilungDAO.class);
	
	/**
	* SQL Datasource. In order to work with data from a database, we need to
	* obtain a connection to the database. A Datasource is part of the JDBC
	* specification and can be seen as a generalized connection factory.
	*/
	private DataSource dataSource = null;
	
	/**
	* Transaction Manager. For encapsulating insert and updates in transaction.
	*/
	private PlatformTransactionManager transactionManager = null;
	
	/**
	* SQL Query Strings.
	*/
	private String sql_getAllAbteilungen = "";
	private String sql_getAbteilung = "";
	private String sql_insertAbteilung = "";
	private String sql_updateAbteilung = "";
	private String sql_deleteAbteilung = "";
	
	/**
	* Query Objects.
	*/
	private Query_GetAbteilung query_getAbteilung;
	private Query_InsertAbteilung query_insertAbteilung;
	private Query_UpdateAbteilung query_updateAbteilung;
	private Query_DeleteAbteilung query_deleteAbteilung;
	private Query_GetAllAbteilungen query_getAllAbteilungen;
	
	/** ******************************************************************* */
	/** ******************************************************************* */
	/* INNER CLASSES TO DEFINE SQL STATEMENTS AND MAPPINGS */
	/** ******************************************************************* */
	/** ******************************************************************* */
	
	/**
	* Defines the base class used for all other queries. This is a private
	* inner class, as this is used only in this data access object
	*/
	private class BaseAbteilungsQuery extends MappingSqlQuery {
		/**
		* The Base query is extended by all other queries
		* 
		* @param ds SQL Datasource
		* @see #dataSource
		* @param sql  The SQL search String
		*/
		public BaseAbteilungsQuery(DataSource ds, String sql) {
			super(ds, sql);
		}
		
		protected Abteilung mapRow(ResultSet rs, int rowNumber)
		throws SQLException {
			Abteilung abteilung = new Abteilung();
			abteilung.setBezeichnung(rs.getString("bezeichnung"));
 			abteilung.setAbteilungsleiter(rs.getInt("abteilungsleiter"));
 			return abteilung;
		}
	}
	
	/**
	* Retrieves all abteilungen in the DB. This
	* is a private inner class, as this is used only in this data access
	* object.
	*/
	private class Query_GetAllAbteilungen extends BaseAbteilungsQuery {
		

		/**
		* Fetches all Abteilungen from table
		* 
		* @param ds SQL Datasource
		*            
		* @see #dataSource
		*/
		public Query_GetAllAbteilungen(DataSource ds) {
			super(ds, sql_getAllAbteilungen);
			compile();
		}
		
	}
	
	
	/**
	* Retrieves one Abteilung from the DB. This is a private inner class, as this
	* is used only in this data access object
	*/
	private class Query_GetAbteilung extends BaseAbteilungsQuery {
		
		/**
		* Fetches only ONE Abteilung from database.
		* 
		* @param ds  SQL Datasource
		* @see #dataSource
		*/
		public Query_GetAbteilung(DataSource ds) {
			super(ds, sql_getAbteilung);
			super.declareParameter(new SqlParameter("bezeichnung", Types.VARCHAR));
			compile();
		}

	}
	
	/**
	* Adds one Abteilung to database. This is a private inner class, as this is
	* used only in this data access object.
	* SqlUpdate is a helper class provided by the Spring-Framework
	*/
	private class Query_InsertAbteilung extends SqlUpdate {
		/**
		* Retrieves the bezeichnung of the Abteilung added to database.
		* 
		* @param ds  SQL Datasource
		* @see #dataSource
		*/
		public Query_InsertAbteilung(DataSource ds) {
			setDataSource(ds);
			setSql(sql_insertAbteilung);
			declareParameter(new SqlParameter("bezeichnung", Types.VARCHAR));
 			declareParameter(new SqlParameter("abteilungsleiter", Types.INTEGER));
 		}
	}
	
	/**
	* Updates one Abteilung in the database. This is a private inner class, as
	* this is used only in this data access object.
	* SqlUpdate is a helper class provided by the Spring-Framework
	*/
	private class Query_UpdateAbteilung extends SqlUpdate {
		/**
		* Create and initialises the SQL-Query for updating a Abteilung.
		* 
		* @param ds  SQL Datasource
		* @see #dataSource
		*/
		public Query_UpdateAbteilung(DataSource ds) {
			setDataSource(ds);
			setSql(sql_updateAbteilung);
 			declareParameter(new SqlParameter("bezeichnung", Types.VARCHAR));
			declareParameter(new SqlParameter("abteilungsleiter", Types.INTEGER));
 		}
	}
	
	/**
	* Deletes one Abteilung from the database. This is a private inner class, as
	* this is used only in this data access object.
	*/
	private class Query_DeleteAbteilung extends SqlUpdate {
		/**
		* Creates and initializes the SQL-Query for deleting a Abteilung.
		* 
		* @param ds  SQL Datasource
		* @see #dataSource
		*/
		public Query_DeleteAbteilung(DataSource ds) {
			setDataSource(ds);
			setSql(sql_deleteAbteilung);
			declareParameter(new SqlParameter("bezeichnung", Types.VARCHAR));
		}
	}

	
	/** ******************************************************************* */
	/** ******************************************************************* */
	/* C O N S T R U C T O R */
	/** ******************************************************************* */
	/** ******************************************************************* */
	
	public JDBCAbteilungDAO() {
		super();
	}
	
	/**
	* The Initialise Method must be called after all bean values are set,
	* particularly the datasource and the transaction manager. This is actually
	* performed by the Spring Framework, which sets first of all, all Java Bean
	* Properties and eventually calls this init method (see bean definition in
	* beans.xml configuration file)
	*/
	public void init() {
		log.info("Initialise AbteilungDAO");
		query_getAbteilung = new Query_GetAbteilung(dataSource);
		query_insertAbteilung = new Query_InsertAbteilung(dataSource);
		query_updateAbteilung = new Query_UpdateAbteilung(dataSource);
		query_deleteAbteilung = new Query_DeleteAbteilung(dataSource);
		query_getAllAbteilungen = new Query_GetAllAbteilungen(dataSource);
	}
	
	/**
	* The Destroy Method is called by the Spring Framework to end the lifecycle
	* of this bean, but <b>only</b> when the bean is created as singleton.
	* Check the bean definition in beans.xml configuration file for details.
	*/
	public void destroy() {
		log.info("Destroy AbteilungDAO");
	}
	
	/** ******************************************************************* */
	/** ******************************************************************* */
	/*
	* BEAN SETTERS FOR DEPENDENCY INJECTION
	* 
	* Dependency Injection is a design pattern in which the responsibility for
	* object creation and object linking is removed from the objects themselves
	* and transferred to a factory object. It is a way to achieve loose
	* coupling between objects and results in highly testable objects
	* (controlled unit tests).
	* 
	* Factory Object: (Design Pattern) is an object for creating other objects.
	* Typically a factory has a method for every kind of object it is capable
	* of creating. these methods optionally accept parameters defining how the
	* object is created and then return the created object.
	*/
	/** ******************************************************************* */
	/** ******************************************************************* */
	
	/**
	* Sets the SQL String to get all Abteilung.
	* 
	* @param sql_getAllAbteilungen
	*            SQL Statement as String
	*/
	public void setSql_getAllAbteilungen(String sql_getAllAbteilungen) {
		this.sql_getAllAbteilungen = sql_getAllAbteilungen;
	}
	
	/**
	* Sets the SQL String to get one Abteilung with one SQL parameter.
	* 
	* @param sql_getAbteilung
	*            SQL Statement as String
	*/
	public void setSql_getAbteilung(String sql_getAbteilung) {
		this.sql_getAbteilung = sql_getAbteilung;
	}
	
	/**
	* Sets the SQL String to insert one Abteilung into the database.
	* 
	* @param sql_insertAbteilung
	*            SQL Statement as String
	*/
	public void setSql_insertAbteilung(String sql_insertAbteilung) {
		this.sql_insertAbteilung = sql_insertAbteilung;
	}
	
	/**
	* Sets the SQL String to update an Abteilung.
	* 
	* @param sql_updateAbteilung
	*            SQL Statement as String
	*/
	public void setSql_updateAbteilung(String sql_updateAbteilung) {
		this.sql_updateAbteilung = sql_updateAbteilung;
	}
	
	/**
	* Sets the SQL String to delete an Abteilung.
	* 
	* @param sql_deleteAbteilung
	*            SQL Statement as String
	*/
	public void setSql_deleteAbteilung(String sql_deleteAbteilung) {
		this.sql_deleteAbteilung = sql_deleteAbteilung;
	}
	

	/**
	* Sets the Datasource to connect to database.
	* 
	* @param dataSource
	*            SQL Datasource
	* @see #dataSource
	*/
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	/**
	* Sets the transaction manager.
	* 
	* @param transactionManager
	*            central interface in Spring's transaction infrastructure
	* @see #transactionManager
	*/
	public void setTransactionManager(PlatformTransactionManager transactionManager) {
		this.transactionManager = transactionManager;
	}
		
		/** ******************************************************************* */
		/** ******************************************************************* */
		/*
		* DAO METHODS
		* 
		* A Data Access Object (DAO) is a component which provides a common
		* interface between the application and one or more data storage devices,
		* such as a database or a file. The advantage of using data access objects
		* is that any business object (which contains application or operation
		* specific details) does not require direct knowledge of the final
		* destination for the information it manipulates. As a result, _if_ it is
		* necessary to change where or how the data is stored, these modifications
		* can be made without needing to change the main application.
		*/
		/** ******************************************************************* */
		/** ******************************************************************* */
		
		
		/**
		* Inserts one Abteilung into the database. 
		* 
		* @param Abteilung
		*            Abteilung object holding data of one Abteilung
		* @return 
		*/
		public Abteilung add(Abteilung abteilung) {
			log.info("Add Abteilung Bezeichnung = " + abteilung.getBezeichnung() + " "
				+ abteilung.getAbteilungsleiter());
			log.debug("Initialise SQL Parameters");
			final Object[] param = new Object[] {abteilung.getBezeichnung(),
				abteilung.getAbteilungsleiter()};
			int update = query_insertAbteilung.update(param);
			if (update == 1){
				log.info("Adding abteilung \""+abteilung.getBezeichnung()+"\" has been successfull!");
				return abteilung;
			} else {
				log.info("error in adding abteilung \""+abteilung.getBezeichnung()+"\" " +
						"- maybe an abteilung with this name already exists!");
				return null;
			}
		}
		
		public Abteilung update(Abteilung abteilung) {
			return updateAbteilung(abteilung, abteilung.getBezeichnung());
		}
		
		public Abteilung updateAbteilung(Abteilung abteilung, String bez) {
			log.info("Update Abteilung, bez = " + abteilung.getBezeichnung());
			log.debug("Initialise SQL Parameters");
			final Object[] param = new Object[] {abteilung.getAbteilungsleiter(), bez};
			log.debug("Execute Update");
			if (query_updateAbteilung.update(param) == 1) {
				log.debug("Update Successfull");
				return abteilung;
			}
			log.error("Update for abteilung = " + bez + " failed.");
			return null;
		}
		
		/**
		* Retrieves all Abteilungen from the database. <br>
		*/
		@SuppressWarnings("unchecked")
		public List<Abteilung> getAllAbteilungen() {
			log.info("Get all Abteilungen");
			List<Abteilung> abt = null;
			abt = query_getAllAbteilungen.execute();
			log.debug("Abteilungen List contains " + abt.size() + " Abteilungen");
			return abt;
		}
		
	public boolean delete(String bez) {
		log.info("Delete Abteilung bez = " + bez);
		log.debug("Initialize SQL Parameters");
		final Object[] param = new Object[] { bez };
		log.debug("Executing SQL");
		if (query_deleteAbteilung.update(param) == 1) {
			log.debug("Deleting successfull");
			return true;
		} else { 
			log.error("Deleting Abteilung bez = " + bez + " failed, no such Abteilung found!");
			return false;
		}
	}
	
	public Abteilung getAbteilung(String bez){
		List <Abteilung> abt = query_getAbteilung.execute(bez);
		if (abt.size() == 1) {
			Abteilung a = abt.get(0);
			log.debug("Getting abteilung "+bez+" successfull");
			return a;
		} else { 
			log.error("No abteilung "+bez+" found!");
			return null;
		}
	}
	
	public List<Abteilung> getAbteilungen() {
		log.info("Get all Abteilungen");
		List<Abteilung> abt = null;
		abt = query_getAllAbteilungen.execute();
		log.debug("Abteilungen List contains " + abt.size() + " Abteilungen");
		return abt;
	}
	
	
	public boolean deleteAll() {
		// TODO Auto-generated method stub
		return false;
	}
	
	public List<Abteilung> getAll() {
		return getAbteilungen();
	}


	public Abteilung getById(int id) {
		//gibt keine ID bei abteilung, sondern bezeichnung (string) 
		//ist die eindeutige kennzeichnung..
		//deshalb ist diese methode aus IGenericDAO auch nicht implementiert!
		return null;
	}

	public boolean delete(int typId) {
		//gibt keine ID bei abteilung, sondern bezeichnung (string) 
		//ist die eindeutige kennzeichnung..
		//deshalb ist diese methode aus IGenericDAO auch nicht implementiert!
		return false;
	}

	

}
